package com.pearl.db;

import com.pearl.entitys.dataBaseTable.User;
import com.pearl.utils.DateUtils;
import com.pearl.utils.Where;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

  private final String SQL_INS = "INSERT INTO user(user_id,user_name,password,salt,email,phone,sex,age,status,create_time,update_time) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
  private final String SQL_UPD = "UPDATE user SET user_name=?,password=?,salt=?,email=?,phone=?,sex=?,age=?,status=?,create_time=?,update_time=? WHERE user_id=?";
  private final String SQL_SEL = "SELECT user_id,user_name,password,salt,email,phone,sex,age,status,create_time,update_time FROM user ";
  private final Connection conn;

  public UserDao(Connection conn) {
    this.conn = conn;
  }

  public User selectbyUserId(String userId) throws SQLException {
    User result = null;
    try (PreparedStatement ps = conn.prepareStatement(SQL_SEL + "WHERE userId = ?")) {
      ps.setString(1, userId);
      ResultSet rs = ps.executeQuery();
      result = convert(rs);
      return result;
    } catch (SQLException e) {
      throw new SQLException(e.getMessage());
    }
  }

  public List<User> selectAll() throws SQLException {
    ArrayList<User> result = new ArrayList<User>();
    try (PreparedStatement ps = this.conn.prepareStatement(SQL_SEL)) {
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        result.add(convert(rs));
      }
      return result;
    }
  }

  public int insert(User data) throws SQLException {
    try (PreparedStatement ps = this.conn.prepareStatement(SQL_INS)) {
      ps.setString(1, data.getUserId());
      ps.setString(2, data.getUserName());
      ps.setString(3, data.getPassword());
      ps.setString(4, data.getSalt());
      ps.setString(5, data.getEmail());
      ps.setString(6, data.getPhone());
      ps.setString(7, data.getSex());
      ps.setInt(8, data.getAge());
      ps.setInt(9, data.getStatus());
      DateUtils.setDateTz(ps, 10, data.getCreateTime());
      DateUtils.setDateTz(ps, 11, data.getUpdateTime());
      return ps.executeUpdate();
    }
  }


  public int insert(List<User> dataList) throws SQLException {
    try (PreparedStatement ps = this.conn.prepareStatement(SQL_INS)) {
      for (User data : dataList) {
        ps.setString(1, data.getUserId());
        ps.setString(2, data.getUserName());
        ps.setString(3, data.getPassword());
        ps.setString(4, data.getSalt());
        ps.setString(5, data.getEmail());
        ps.setString(6, data.getPhone());
        ps.setString(7, data.getSex());
        ps.setInt(8, data.getAge());
        ps.setInt(9, data.getStatus());
        DateUtils.setDateTz(ps, 10, data.getCreateTime());
        DateUtils.setDateTz(ps, 11, data.getUpdateTime());
        ps.addBatch();
      }
      return ps.executeBatch().length;
    }
  }


  public int update(User data) throws SQLException {
    try (PreparedStatement ps = this.conn.prepareStatement(SQL_UPD)) {
      ps.setString(1, data.getUserName());
      ps.setString(2, data.getPassword());
      ps.setString(3, data.getSalt());
      ps.setString(4, data.getEmail());
      ps.setString(5, data.getPhone());
      ps.setString(6, data.getSex());
      ps.setInt(7, data.getAge());
      ps.setInt(8, data.getStatus());
      DateUtils.setDateTz(ps, 9, data.getCreateTime());
      DateUtils.setDateTz(ps, 10, data.getUpdateTime());
      ps.setString(11, data.getUserId());
      return ps.executeUpdate();
    }
  }

  public List<User> selectByWhere(Where where) throws SQLException {
    ArrayList<User> result = new ArrayList<User>();
    try (PreparedStatement ps = where.prepareStatement(this.conn, SQL_SEL)) {
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        result.add(convert(rs));
      }
      return result;
    }
  }

  private User convert(ResultSet rs) throws SQLException {
    User data = new User();
    int index = 1;
    data.setUserId(rs.getString(index++));
    data.setUserName(rs.getString(index++));
    data.setPassword(rs.getString(index++));
    data.setSalt(rs.getString(index++));
    data.setEmail(rs.getString(index++));
    data.setPhone(rs.getString(index++));
    data.setSex(rs.getString(index++));
    data.setAge(rs.getInt(index++));
    data.setStatus(rs.getInt(index++));
    data.setCreateTime(DateUtils.getDateTz(rs, index++));
    data.setUpdateTime(DateUtils.getDateTz(rs, index++));
    return data;

  }
}
